package com.itfreer.xjmh.cycle;


import com.itfreer.form.api.JdbcBaseDaoImp;
import com.itfreer.xjmh.cycle.dao.CycleAirSecurityShowDao;
import com.itfreer.xjmh.temporary.dao.TemporaryShowDao;
import org.hibernate.query.internal.NativeQueryImpl;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Component
public class jdbcCycleAirSecurityShowDao extends JdbcBaseDaoImp implements CycleAirSecurityShowDao {


    public jdbcCycleAirSecurityShowDao(EntityManager entityManager) {
        super(entityManager);
    }

    @Override
    public List<Map<String, Object>> getShowData(String startTime, String endTime, String airport, String adminId) {
        // 自定义查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = "SELECT\n" +
                "\tadmin.airport AS airport,\n" +
                "\tadmin.airport_name AS airportName,\n" +
                "\tSUM(admin.administration_check) AS administrationcheck,\n" +
                "\tSUM(admin.security_audit) AS adminsecurityaudit,\n" +
                "\tSUM(admin.security_test) AS adminsecuritytest,\n" +
                "\tSUM(admin.change_notice) AS adminchangenotice,\n" +
                "\tSUM(admin.change_advice) AS adminchangeadvice,\n" +
                "\tSUM(admin.administration_publish) AS administrationpublish,\n" +
                "\tSUM(admin.administration_talk) AS administrationtalk,\n" +
                "\t'' policepoliceillegalevent,\n" +
                "\t'' policepolicedisturbevent,\n" +
                "\t'' policegchartercount,\n" +
                "\t'' policegchartersendperson,\n" +
                "\t'' policegflightprotectperson,\n" +
                "\t'' policegflightprotectflight,\n" +
                "\t'' policegflightsendperson,\n" +
                "\t'' policepcriminalsolvecase,\n" +
                "\t'' policepcriminalarrestperson,\n" +
                "\t'' policepcriminalescapeperson,\n" +
                "\t'' policepterroristsolvecase,\n" +
                "\t'' policepterroristarrestperson,\n" +
                "\t'' policepgrouphandle,\n" +
                "\t'' policepgroupsendperson,\n" +
                "\t'' policepfirecount,\n" +
                "\t'' policepfirelose,\n" +
                "\t'' policepfirecheck,\n" +
                "\t'' policepfirechange,\n" +
                "\t'' policepdrugsolvecase,\n" +
                "\t'' policepdrugcount,\n" +
                "\t'' policepdrugpersoncount,\n" +
                "\t'' policepfaketerroristhandle,\n" +
                "\t'' policepfaketerroristsolve,\n" +
                "\t'' policepfaketerroristdetain,\n" +
                "\t'' policepfaketerroristpublish,\n" +
                "\t'' policeptransportcase,\n" +
                "\t'' policeptransportpublish,\n" +
                "\t'' policeptransportillegal,\n" +
                "\t'' policeptransportaccident,\n" +
                "\t'' policeafcheckalarm,\n" +
                "\t'' policeafflightalarm,\n" +
                "\t'' policeafcriminalcase,\n" +
                "\t'' policeafpublish,\n" +
                "\t'' policeafreject,\n" +
                "\t'' policeicheckunit,\n" +
                "\t'' policeicheckimportunit,\n" +
                "\t'' policeicheckhidedanger,\n" +
                "\t'' policeisendnotice,\n" +
                "\t'' policeiholdteach,\n" +
                "\t'' policeicrinimal,\n" +
                "\t'' policeiprotectperson,\n" +
                "\t'' policeidetain,\n" +
                "\t'' aircheckbag,\n" +
                "\t'' airdanger,\n" +
                "\t'' airguns,\n" +
                "\t'' aircontrolknife,\n" +
                "\t'' airgoodspackages,\n" +
                "\t'' airfakename,\n" +
                "\t'' airdangergoods,\n" +
                "\t'' airafalarm,\n" +
                "\t'' companysendperson,\n" +
                "\t'' companyflightcount,\n" +
                "\t'' companyflyingtime,\n" +
                "\t'' companyillegalevent,\n" +
                "\t'' companydisturbevent,\n" +
                "\t'' companyhelpescort,\n" +
                "\t'' companyreturnperson,\n" +
                "\t'' companyafalarm\n" +
                "FROM\n" +
                "\tt_cycle_air_security_supervision admin \n" +
                "WHERE\n" +
                "\t( admin.s_create_time <= '" + endTime + "' AND admin.s_create_time >= '" + startTime + "' )\n";
                if (airport != null && airport.length() > 0) {
                    sql = sql + "\tAND admin.airport = "+ airport + "\n";
                }
                sql += "GROUP BY\n" +
                "\tairport UNION ALL\n" +
                "SELECT\n" +
                "\tjc.id AS airport,\n" +
                "\tjc.s_display AS airportName,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' adminsecurityaudit,\n" +
                "\t'' adminsecuritytest,\n" +
                "\t'' adminchangenotice,\n" +
                "\t'' adminchangeadvice,\n" +
                "\t'' administrationpublish,\n" +
                "\t'' administrationtalk,\n" +
                "\tSUM(police.illegal_event) AS polictillegalevent,\n" +
                "\tSUM(police.disturb_event) AS disturbevent,\n" +
                "\tSUM(police.g_charter_count) AS polictgchartercount,\n" +
                "\tSUM(police.g_charter_send_person) AS polictgchartersendperson,\n" +
                "\tSUM(police.g_flight_protect_person) AS polictgflightprotectperson,\t\n" +
                "\tSUM(police.g_flight_protect_flight) AS polictgflightprotectflight,\n" +
                "\tSUM(police.g_flight_send_person) AS polictgflightsendperson,\n" +
                "\tSUM(police.p_criminal_solve_case) AS polictpcriminalsolvecase,\n" +
                "\tSUM(police.p_criminal_arrest_person) AS polictpcriminalarrestperson,\n" +
                "\tSUM(police.p_criminal_escape_person) AS polictpcriminalescapeperson,\n" +
                "\tSUM(police.p_terrorist_solve_case) AS polictpterroristsolvecase,\n" +
                "\tSUM(police.p_terrorist_arrest_person) AS polictpterroristarrestperson,\n" +
                "\tSUM(police.p_group_handle) AS polictpgrouphandle,\n" +
                "\tSUM(police.p_group_send_person) AS polictpgroupsendperson,\n" +
                "\tSUM(police.p_fire_count) AS polictpfirecount,\n" +
                "\tSUM(police.p_fire_lose) AS polictpfirelose,\n" +
                "\tSUM(police.p_fire_check) AS polictpfirecheck,\n" +
                "\tSUM(police.p_fire_change) AS polictpfirechange,\n" +
                "\tSUM(police.p_drug_solve_case) AS polictpdrugsolvecase,\n" +
                "\tSUM(police.p_drug_count) AS polictpdrugcount,\n" +
                "\tSUM(police.p_drug_person_count) AS polictpdrugpersoncount,\n" +
                "\tSUM(police.p_fake_terrorist_handle) AS polictpfaketerroristhandle,\n" +
                "\tSUM(police.p_fake_terrorist_solve) AS polictpfaketerroristsolve,\n" +
                "\tSUM(police.p_fake_terrorist_detain) AS polictpfaketerroristdetain,\n" +
                "\tSUM(police.p_fake_terrorist_publish) AS polictpfaketerroristpublish,\n" +
                "\tSUM(police.p_transport_case) AS polictptransportcase,\n" +
                "\tSUM(police.p_transport_publish) AS polictptransportpublish,\n" +
                "\tSUM(police.p_transport_illegal) AS polictptransportillegal,\n" +
                "\tSUM(police.p_transport_accident) AS polictptransportaccident,\n" +
                "\tSUM(police.af_check_alarm) AS polictafcheckalarm,\n" +
                "\tSUM(police.af_flight_alarm) AS polictafflightalarm,\n" +
                "\tSUM(police.af_criminal_case) AS polictafcriminalcase,\n" +
                "\tSUM(police.af_publish) AS polictafpublish,\n" +
                "\tSUM(police.af_reject) AS polictafreject,\n" +
                "\tSUM(police.i_check_unit) AS policticheckunit,\n" +
                "\tSUM(police.i_check_import_unit) AS policticheckimportunit,\n" +
                "\tSUM(police.i_check_hide_danger) AS policticheckhidedanger,\n" +
                "\tSUM(police.i_send_notice) AS polictisendnotice,\n" +
                "\tSUM(police.i_hold_teach) AS polictiholdteach,\n" +
                "\tSUM(police.i_crinimal) AS policticrinimal,\n" +
                "\tSUM(police.i_protect_person) AS polictiprotectperson,\n" +
                "\tSUM(police.i_detain) AS polictidetain,\n" +
                "\t\n" +
                "\tSUM(air.c_check_bag) AS aircheckbag,\n" +
                "\tSUM(air.c_danger) AS airdanger,\n" +
                "\tSUM(air.c_guns) AS airguns,\n" +
                "\tSUM(air.c_control_knife) AS aircontrolknife,\n" +
                "\tSUM(air.c_goods_packages) AS airgoodspackages,\n" +
                "\tSUM(air.c_fake_name) AS airfakename,\n" +
                "\tSUM(air.c_danger_goods) AS airdangergoods,\n" +
                "\tSUM(air.af_alarm) AS airafalarm,\n" +
                "\t\n" +
                "\tSUM(company.a_send_person) AS componysendperson,\n" +
                "\tSUM(company.a_flight_count) AS componyflightcount,\n" +
                "\tSUM(company.a_flying_time) AS componyflyingtime,\n" +
                "\tSUM(company.a_illegal_event) AS componyillegalevent,\n" +
                "\tSUM(company.a_disturb_event) AS componydisturbevent,\n" +
                "\tSUM(company.a_help_escort) AS componyhelpescort,\n" +
                "\tSUM(company.a_return_person) AS componyreturnperson,\n" +
                "\tSUM(company.af_alarm) AS componyafalarm\n" +
                "\n" +
                "FROM\n" +
                "\t( SELECT id, s_display FROM data_dictionary WHERE s_dicname = 'zd_airport' ) jc\n" +
                "\tLEFT JOIN t_cycle_air_security_police police ON jc.id = police.airport\n" +
                "\tLEFT JOIN t_cycle_air_security_airport air ON jc.id = air.airport\n" +
                "\tLEFT JOIN t_cycle_air_security_company company ON jc.id = company.airport \n" +
                "WHERE\n" +
                "\t(\n" +
                "\t( air.s_create_time <= '" + endTime + "' AND air.s_create_time >= '" + startTime + "' )\n" +
                "\tOR (police.s_create_time <= '" + endTime + "' AND police.s_create_time >= '" + startTime + "' )\n" +
                "\tOR (company.s_create_time <= '" + endTime + "' AND company.s_create_time >= '" + startTime + "' ))\n";
                if (airport != null && airport.length() > 0){
                    sql += "\tAND jc.id = "+ airport +"\n";
                }
                sql += " GROUP BY\n" +
                        "\tairport";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        list = query.getResultList();
        return list;
    }

    @Override
    public List<Map<String, Object>> getShowDate() {
        // 自定义查询
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        String sql = "SELECT date,\n" +
                "\t SUM(administrationcheck) AS administrationcheck,\n" +
                "\t SUM(adminsecurityaudit) AS adminsecurityaudit,\n" +
                "\t SUM(adminsecuritytest) AS adminsecuritytest,\n" +
                "\t SUM(adminchangenotice) AS adminchangenotice,\n" +
                "\t SUM(adminchangeadvice) AS adminchangeadvice,\n" +
                "\t SUM(administrationpublish) AS administrationpublish,\n" +
                "\t SUM(administrationtalk) AS administrationtalk,\n" +
                "\t SUM(policepoliceillegalevent) AS policepoliceillegalevent,\n" +
                "\t SUM(policepolicedisturbevent) AS policepolicedisturbevent,\n" +
                "\t SUM(policegchartercount) AS policegchartercount,\n" +
                "\t SUM(policegchartersendperson) AS policegchartersendperson,\n" +
                "\t SUM(policegflightprotectperson) AS policegflightprotectperson,\n" +
                "\t SUM(policegflightprotectflight) AS policegflightprotectflight,\n" +
                "\t SUM(policegflightsendperson) AS policegflightsendperson,\n" +
                "\t SUM(policepcriminalsolvecase) AS policepcriminalsolvecase,\n" +
                "\t SUM(policepcriminalarrestperson) AS policepcriminalarrestperson,\n" +
                "\t SUM(policepcriminalescapeperson) AS policepcriminalescapeperson,\n" +
                "\t SUM(policepterroristsolvecase) AS policepterroristsolvecase,\n" +
                "\t SUM(policepterroristarrestperson) AS policepterroristarrestperson,\n" +
                "\t SUM(policepgrouphandle) AS policepgrouphandle,\n" +
                "\t SUM(policepgroupsendperson) AS policepgroupsendperson,\n" +
                "\t SUM(policepfirecount) AS policepfirecount,\n" +
                "\t SUM(policepfirelose) AS policepfirelose,\n" +
                "\t SUM(policepfirecheck) AS policepfirecheck,\n" +
                "\t SUM(policepfirechange) AS policepfirechange,\n" +
                "\t SUM(policepdrugsolvecase) AS policepdrugsolvecase,\n" +
                "\t SUM(policepdrugcount) AS policepdrugcount,\n" +
                "\t SUM(policepdrugpersoncount) AS policepdrugpersoncount,\n" +
                "\t SUM(policepfaketerroristhandle) AS policepfaketerroristhandle,\n" +
                "\t SUM(policepfaketerroristsolve) AS policepfaketerroristsolve,\n" +
                "\t SUM(policepfaketerroristdetain) AS policepfaketerroristdetain,\n" +
                "\t SUM(policepfaketerroristpublish) AS policepfaketerroristpublish,\n" +
                "\t SUM(policeptransportcase) AS policeptransportcase,\n" +
                "\t SUM(policeptransportpublish) AS policeptransportpublish,\n" +
                "\t SUM(policeptransportillegal) AS policeptransportillegal,\n" +
                "\t SUM(policeptransportaccident) AS policeptransportaccident,\n" +
                "\t SUM(policeafcheckalarm) AS policeafcheckalarm,\n" +
                "\t SUM(policeafflightalarm) AS policeafflightalarm,\n" +
                "\t SUM(policeafcriminalcase) AS policeafcriminalcase,\n" +
                "\t SUM(policeafpublish) AS policeafpublish,\n" +
                "\t SUM(policeafreject) AS policeafreject,\n" +
                "\t SUM(policeicheckunit) AS policeicheckunit,\n" +
                "\t SUM(policeicheckimportunit) AS policeicheckimportunit,\n" +
                "\t SUM(policeicheckhidedanger) AS policeicheckhidedanger,\n" +
                "\t SUM(policeisendnotice) AS policeisendnotice,\n" +
                "\t SUM(policeiholdteach) AS policeiholdteach,\n" +
                "\t SUM(policeicrinimal) AS policeicrinimal,\n" +
                "\t SUM(policeiprotectperson) AS policeiprotectperson,\n" +
                "\t SUM(policeidetain) AS policeidetain,\n" +
                "\t SUM(aircheckbag) AS aircheckbag,\n" +
                "\t SUM(airdanger) AS airdanger,\n" +
                "\t SUM(airguns) AS airguns,\n" +
                "\t SUM(aircontrolknife) AS aircontrolknife,\n" +
                "\t SUM(airgoodspackages) AS airgoodspackages,\n" +
                "\t SUM(airfakename) AS airfakename,\n" +
                "\t SUM(airdangergoods) AS airdangergoods,\n" +
                "\t SUM(airafalarm) AS airafalarm,\n" +
                "\t SUM(companysendperson) AS companysendperson,\n" +
                "\t SUM(companyflightcount) AS companyflightcount,\n" +
                "\t SUM(companyflyingtime) AS companyflyingtime,\n" +
                "\t SUM(companyillegalevent) AS companyillegalevent,\n" +
                "\t SUM(companydisturbevent) AS companydisturbevent,\n" +
                "\t SUM(companyhelpescort) AS companyhelpescort,\n" +
                "\t SUM(companyreturnperson) AS companyreturnperson,\n" +
                "\t SUM(companyafalarm) AS  companyafalarm\n" +
                "\tFROM (SELECT\n" +
                "\tDATE_FORMAT(s_create_time,\"%Y/%m\") as date,\n" +
                "\tSUM(admin.administration_check) AS administrationcheck,\n" +
                "\tSUM(admin.security_audit) AS adminsecurityaudit,\n" +
                "\tSUM(admin.security_test) AS adminsecuritytest,\n" +
                "\tSUM(admin.change_notice) AS adminchangenotice,\n" +
                "\tSUM(admin.change_advice) AS adminchangeadvice,\n" +
                "\tSUM(admin.administration_publish) AS administrationpublish,\n" +
                "\tSUM(admin.administration_talk) AS administrationtalk,\n" +
                "\t'' policepoliceillegalevent,\n" +
                "\t'' policepolicedisturbevent,\n" +
                "\t'' policegchartercount,\n" +
                "\t'' policegchartersendperson,\n" +
                "\t'' policegflightprotectperson,\n" +
                "\t'' policegflightprotectflight,\n" +
                "\t'' policegflightsendperson,\n" +
                "\t'' policepcriminalsolvecase,\n" +
                "\t'' policepcriminalarrestperson,\n" +
                "\t'' policepcriminalescapeperson,\n" +
                "\t'' policepterroristsolvecase,\n" +
                "\t'' policepterroristarrestperson,\n" +
                "\t'' policepgrouphandle,\n" +
                "\t'' policepgroupsendperson,\n" +
                "\t'' policepfirecount,\n" +
                "\t'' policepfirelose,\n" +
                "\t'' policepfirecheck,\n" +
                "\t'' policepfirechange,\n" +
                "\t'' policepdrugsolvecase,\n" +
                "\t'' policepdrugcount,\n" +
                "\t'' policepdrugpersoncount,\n" +
                "\t'' policepfaketerroristhandle,\n" +
                "\t'' policepfaketerroristsolve,\n" +
                "\t'' policepfaketerroristdetain,\n" +
                "\t'' policepfaketerroristpublish,\n" +
                "\t'' policeptransportcase,\n" +
                "\t'' policeptransportpublish,\n" +
                "\t'' policeptransportillegal,\n" +
                "\t'' policeptransportaccident,\n" +
                "\t'' policeafcheckalarm,\n" +
                "\t'' policeafflightalarm,\n" +
                "\t'' policeafcriminalcase,\n" +
                "\t'' policeafpublish,\n" +
                "\t'' policeafreject,\n" +
                "\t'' policeicheckunit,\n" +
                "\t'' policeicheckimportunit,\n" +
                "\t'' policeicheckhidedanger,\n" +
                "\t'' policeisendnotice,\n" +
                "\t'' policeiholdteach,\n" +
                "\t'' policeicrinimal,\n" +
                "\t'' policeiprotectperson,\n" +
                "\t'' policeidetain,\n" +
                "\t'' aircheckbag,\n" +
                "\t'' airdanger,\n" +
                "\t'' airguns,\n" +
                "\t'' aircontrolknife,\n" +
                "\t'' airgoodspackages,\n" +
                "\t'' airfakename,\n" +
                "\t'' airdangergoods,\n" +
                "\t'' airafalarm,\n" +
                "\t'' companysendperson,\n" +
                "\t'' companyflightcount,\n" +
                "\t'' companyflyingtime,\n" +
                "\t'' companyillegalevent,\n" +
                "\t'' companydisturbevent,\n" +
                "\t'' companyhelpescort,\n" +
                "\t'' companyreturnperson,\n" +
                "\t'' companyafalarm\n" +
                "FROM\n" +
                "\tt_cycle_air_security_supervision admin \n" +
                "GROUP BY DATE_FORMAT(admin.s_create_time,\"%Y/%m\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_create_time,\"%Y/%m\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' adminsecurityaudit,\n" +
                "\t'' adminsecuritytest,\n" +
                "\t'' adminchangenotice,\n" +
                "\t'' adminchangeadvice,\n" +
                "\t'' administrationpublish,\n" +
                "\t'' administrationtalk,\n" +
                "\tSUM(police.illegal_event) AS polictillegalevent,\n" +
                "\tSUM(police.disturb_event) AS disturbevent,\n" +
                "\tSUM(police.g_charter_count) AS polictgchartercount,\n" +
                "\tSUM(police.g_charter_send_person) AS polictgchartersendperson,\n" +
                "\tSUM(police.g_flight_protect_person) AS polictgflightprotectperson,\t\n" +
                "\tSUM(police.g_flight_protect_flight) AS polictgflightprotectflight,\n" +
                "\tSUM(police.g_flight_send_person) AS polictgflightsendperson,\n" +
                "\tSUM(police.p_criminal_solve_case) AS polictpcriminalsolvecase,\n" +
                "\tSUM(police.p_criminal_arrest_person) AS polictpcriminalarrestperson,\n" +
                "\tSUM(police.p_criminal_escape_person) AS polictpcriminalescapeperson,\n" +
                "\tSUM(police.p_terrorist_solve_case) AS polictpterroristsolvecase,\n" +
                "\tSUM(police.p_terrorist_arrest_person) AS polictpterroristarrestperson,\n" +
                "\tSUM(police.p_group_handle) AS polictpgrouphandle,\n" +
                "\tSUM(police.p_group_send_person) AS polictpgroupsendperson,\n" +
                "\tSUM(police.p_fire_count) AS polictpfirecount,\n" +
                "\tSUM(police.p_fire_lose) AS polictpfirelose,\n" +
                "\tSUM(police.p_fire_check) AS polictpfirecheck,\n" +
                "\tSUM(police.p_fire_change) AS polictpfirechange,\n" +
                "\tSUM(police.p_drug_solve_case) AS polictpdrugsolvecase,\n" +
                "\tSUM(police.p_drug_count) AS polictpdrugcount,\n" +
                "\tSUM(police.p_drug_person_count) AS polictpdrugpersoncount,\n" +
                "\tSUM(police.p_fake_terrorist_handle) AS polictpfaketerroristhandle,\n" +
                "\tSUM(police.p_fake_terrorist_solve) AS polictpfaketerroristsolve,\n" +
                "\tSUM(police.p_fake_terrorist_detain) AS polictpfaketerroristdetain,\n" +
                "\tSUM(police.p_fake_terrorist_publish) AS polictpfaketerroristpublish,\n" +
                "\tSUM(police.p_transport_case) AS polictptransportcase,\n" +
                "\tSUM(police.p_transport_publish) AS polictptransportpublish,\n" +
                "\tSUM(police.p_transport_illegal) AS polictptransportillegal,\n" +
                "\tSUM(police.p_transport_accident) AS polictptransportaccident,\n" +
                "\tSUM(police.af_check_alarm) AS polictafcheckalarm,\n" +
                "\tSUM(police.af_flight_alarm) AS polictafflightalarm,\n" +
                "\tSUM(police.af_criminal_case) AS polictafcriminalcase,\n" +
                "\tSUM(police.af_publish) AS polictafpublish,\n" +
                "\tSUM(police.af_reject) AS polictafreject,\n" +
                "\tSUM(police.i_check_unit) AS policticheckunit,\n" +
                "\tSUM(police.i_check_import_unit) AS policticheckimportunit,\n" +
                "\tSUM(police.i_check_hide_danger) AS policticheckhidedanger,\n" +
                "\tSUM(police.i_send_notice) AS polictisendnotice,\n" +
                "\tSUM(police.i_hold_teach) AS polictiholdteach,\n" +
                "\tSUM(police.i_crinimal) AS policticrinimal,\n" +
                "\tSUM(police.i_protect_person) AS polictiprotectperson,\n" +
                "\tSUM(police.i_detain) AS polictidetain,\n" +
                "\t\n" +
                "\t'' aircheckbag,\n" +
                "\t'' airdanger,\n" +
                "\t'' airguns,\n" +
                "\t'' aircontrolknife,\n" +
                "\t'' airgoodspackages,\n" +
                "\t'' airfakename,\n" +
                "\t'' airdangergoods,\n" +
                "\t'' airafalarm,\n" +
                "\t'' companysendperson,\n" +
                "\t'' companyflightcount,\n" +
                "\t'' companyflyingtime,\n" +
                "\t'' companyillegalevent,\n" +
                "\t'' companydisturbevent,\n" +
                "\t'' companyhelpescort,\n" +
                "\t'' companyreturnperson,\n" +
                "\t'' companyafalarm \n" +
                "FROM t_cycle_air_security_police police \n" +
                "GROUP BY DATE_FORMAT(police.s_create_time,\"%Y/%m\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_create_time,\"%Y/%m\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' adminsecurityaudit,\n" +
                "\t'' adminsecuritytest,\n" +
                "\t'' adminchangenotice,\n" +
                "\t'' adminchangeadvice,\n" +
                "\t'' administrationpublish,\n" +
                "\t'' administrationtalk,\n" +
                "\t'' policepoliceillegalevent,\n" +
                "\t'' policepolicedisturbevent,\n" +
                "\t'' policegchartercount,\n" +
                "\t'' policegchartersendperson,\n" +
                "\t'' policegflightprotectperson,\n" +
                "\t'' policegflightprotectflight,\n" +
                "\t'' policegflightsendperson,\n" +
                "\t'' policepcriminalsolvecase,\n" +
                "\t'' policepcriminalarrestperson,\n" +
                "\t'' policepcriminalescapeperson,\n" +
                "\t'' policepterroristsolvecase,\n" +
                "\t'' policepterroristarrestperson,\n" +
                "\t'' policepgrouphandle,\n" +
                "\t'' policepgroupsendperson,\n" +
                "\t'' policepfirecount,\n" +
                "\t'' policepfirelose,\n" +
                "\t'' policepfirecheck,\n" +
                "\t'' policepfirechange,\n" +
                "\t'' policepdrugsolvecase,\n" +
                "\t'' policepdrugcount,\n" +
                "\t'' policepdrugpersoncount,\n" +
                "\t'' policepfaketerroristhandle,\n" +
                "\t'' policepfaketerroristsolve,\n" +
                "\t'' policepfaketerroristdetain,\n" +
                "\t'' policepfaketerroristpublish,\n" +
                "\t'' policeptransportcase,\n" +
                "\t'' policeptransportpublish,\n" +
                "\t'' policeptransportillegal,\n" +
                "\t'' policeptransportaccident,\n" +
                "\t'' policeafcheckalarm,\n" +
                "\t'' policeafflightalarm,\n" +
                "\t'' policeafcriminalcase,\n" +
                "\t'' policeafpublish,\n" +
                "\t'' policeafreject,\n" +
                "\t'' policeicheckunit,\n" +
                "\t'' policeicheckimportunit,\n" +
                "\t'' policeicheckhidedanger,\n" +
                "\t'' policeisendnotice,\n" +
                "\t'' policeiholdteach,\n" +
                "\t'' policeicrinimal,\n" +
                "\t'' policeiprotectperson,\n" +
                "\t'' policeidetain,\n" +
                "\tSUM(air.c_check_bag) AS aircheckbag,\n" +
                "\tSUM(air.c_danger) AS airdanger,\n" +
                "\tSUM(air.c_guns) AS airguns,\n" +
                "\tSUM(air.c_control_knife) AS aircontrolknife,\n" +
                "\tSUM(air.c_goods_packages) AS airgoodspackages,\n" +
                "\tSUM(air.c_fake_name) AS airfakename,\n" +
                "\tSUM(air.c_danger_goods) AS airdangergoods,\n" +
                "\tSUM(air.af_alarm) AS airafalarm,\n" +
                "\t\n" +
                "\t'' companysendperson,\n" +
                "\t'' companyflightcount,\n" +
                "\t'' companyflyingtime,\n" +
                "\t'' companyillegalevent,\n" +
                "\t'' companydisturbevent,\n" +
                "\t'' companyhelpescort,\n" +
                "\t'' companyreturnperson,\n" +
                "\t'' companyafalarm \n" +
                "FROM t_cycle_air_security_airport air \n" +
                "GROUP BY DATE_FORMAT(air.s_create_time,\"%Y/%m\") \n" +
                "UNION ALL\n" +
                "SELECT\n" +
                "\tDATE_FORMAT(s_create_time,\"%Y/%m\") as date ,\n" +
                "\t'' administrationcheck,\n" +
                "\t'' adminsecurityaudit,\n" +
                "\t'' adminsecuritytest,\n" +
                "\t'' adminchangenotice,\n" +
                "\t'' adminchangeadvice,\n" +
                "\t'' administrationpublish,\n" +
                "\t'' administrationtalk,\n" +
                "\t'' policepoliceillegalevent,\n" +
                "\t'' policepolicedisturbevent,\n" +
                "\t'' policegchartercount,\n" +
                "\t'' policegchartersendperson,\n" +
                "\t'' policegflightprotectperson,\n" +
                "\t'' policegflightprotectflight,\n" +
                "\t'' policegflightsendperson,\n" +
                "\t'' policepcriminalsolvecase,\n" +
                "\t'' policepcriminalarrestperson,\n" +
                "\t'' policepcriminalescapeperson,\n" +
                "\t'' policepterroristsolvecase,\n" +
                "\t'' policepterroristarrestperson,\n" +
                "\t'' policepgrouphandle,\n" +
                "\t'' policepgroupsendperson,\n" +
                "\t'' policepfirecount,\n" +
                "\t'' policepfirelose,\n" +
                "\t'' policepfirecheck,\n" +
                "\t'' policepfirechange,\n" +
                "\t'' policepdrugsolvecase,\n" +
                "\t'' policepdrugcount,\n" +
                "\t'' policepdrugpersoncount,\n" +
                "\t'' policepfaketerroristhandle,\n" +
                "\t'' policepfaketerroristsolve,\n" +
                "\t'' policepfaketerroristdetain,\n" +
                "\t'' policepfaketerroristpublish,\n" +
                "\t'' policeptransportcase,\n" +
                "\t'' policeptransportpublish,\n" +
                "\t'' policeptransportillegal,\n" +
                "\t'' policeptransportaccident,\n" +
                "\t'' policeafcheckalarm,\n" +
                "\t'' policeafflightalarm,\n" +
                "\t'' policeafcriminalcase,\n" +
                "\t'' policeafpublish,\n" +
                "\t'' policeafreject,\n" +
                "\t'' policeicheckunit,\n" +
                "\t'' policeicheckimportunit,\n" +
                "\t'' policeicheckhidedanger,\n" +
                "\t'' policeisendnotice,\n" +
                "\t'' policeiholdteach,\n" +
                "\t'' policeicrinimal,\n" +
                "\t'' policeiprotectperson,\n" +
                "\t'' policeidetain,\n" +
                "\t'' aircheckbag,\n" +
                "\t'' airdanger,\n" +
                "\t'' airguns,\n" +
                "\t'' aircontrolknife,\n" +
                "\t'' airgoodspackages,\n" +
                "\t'' airfakename,\n" +
                "\t'' airdangergoods,\n" +
                "\t'' airafalarm,\n" +
                "\tSUM(company.a_send_person) AS componysendperson,\n" +
                "\tSUM(company.a_flight_count) AS componyflightcount,\n" +
                "\tSUM(company.a_flying_time) AS componyflyingtime,\n" +
                "\tSUM(company.a_illegal_event) AS componyillegalevent,\n" +
                "\tSUM(company.a_disturb_event) AS componydisturbevent,\n" +
                "\tSUM(company.a_help_escort) AS componyhelpescort,\n" +
                "\tSUM(company.a_return_person) AS componyreturnperson,\n" +
                "\tSUM(company.af_alarm) AS componyafalarm \n" +
                "FROM t_cycle_air_security_company company \n" +
                "GROUP BY DATE_FORMAT(company.s_create_time,\"%Y/%m\") ) t\n" +
                "GROUP BY t.date\n" +
                "ORDER BY t.date";

        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        list = query.getResultList();
        return list;
    }
}
